﻿using System;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Threading.Tasks;
using System.Collections.Generic;

namespace FrameWork.Npoi
{
    /// <summary>
    /// 
    /// </summary>
    public class HSSFExcelHelper : ExcelHelper
    {
        HSSFWorkbook hssfWorkbook;

        /// <summary>
        /// 添加Sheet
        /// </summary>
        /// <param name="sheet"></param>
        public override void Add(ISheet sheet)
        {
            hssfWorkbook.Add(sheet);
        }

        /// <summary>
        /// 创建并初始化工作簿
        /// </summary>
        public override void Init()
        {
            hssfWorkbook = new HSSFWorkbook();
            WorkBook = hssfWorkbook;

            IFont font = WorkBook.GetFontAt(0);
            font.FontName = "宋体";

            SetInformation();
        }
        /// <summary>
        /// 
        /// </summary>
        public override void PreReport()
        {
        }
        /// <summary>
        /// 
        /// </summary>
        public override void Report()
        {
            //PreReport();

            ////下载报表
            //var res = System.Web.HttpContext.Current.Response;
            //res.Clear();
            //res.Buffer = true;
            //res.Charset = "GBK";
            //res.AddHeader("Content-Disposition", "attachment; filename=" + this.ExcelName + ".xlsx");
            //res.ContentEncoding = System.Text.Encoding.GetEncoding("GBK");
            //res.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=GBK";

            ////Wirte Stream 
            //this.WorkBook.Write(res.OutputStream);

            //res.Flush();
            //res.End();
        }

        /// <summary>
        /// <para>配置文件信息</para>
        /// <para>si.Author 填加xls文件作者信息</para>
        /// <para>si.LastAuthor 填加xls文件最后保存者信息</para>
        /// <para>si.Comments </para>
        /// <para>si.Title 填加xls文件标题信息</para>
        /// <para>si.Subject 填加文件主题信息</para>
        /// </summary>
        public override void SetInformation()
        {
            #region 附加信息
            {
                //文档摘要信息
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "深圳芊熠硬件";
                hssfWorkbook.DocumentSummaryInformation = dsi;
                //
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = "Beta"; //填加xls文件作者信息
                si.ApplicationName = "NPOI程序"; //填加xls文件创建程序信息
                si.LastAuthor = "Labbor"; //填加xls文件最后保存者信息
                si.Comments = "深圳芊熠硬件";
                si.Title = "深圳芊熠硬件"; //填加xls文件标题信息
                si.Subject = "深圳芊熠硬件";//填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                hssfWorkbook.SummaryInformation = si;
            }
            #endregion
        }

        public override DataTable ImportToDt(string path)
        {
            hssfWorkbook = CreateWorkbook(path);
            WorkBook = hssfWorkbook;
            return ExportToDataTable(hssfWorkbook.GetSheetAt(0));
        }

        private HSSFWorkbook CreateWorkbook(string path)
        {
            using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook workBook = new HSSFWorkbook(fs);
                return workBook;
            }
        }

        /// <summary>
        /// 把Sheet中的DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private  DataTable ExportToDataTable(ISheet sheet)
        {
            DataTable dt = new DataTable();
            IRow headRow = sheet.GetRow(0);
            for (int i = headRow.FirstCellNum, len = headRow.LastCellNum; i < len; i++)
            {
                dt.Columns.Add(headRow.Cells[i].StringCellValue);
            }
            for (int i = (sheet.FirstRowNum + 1), len = sheet.LastRowNum + 1; i < len; i++)
            {
                IRow tempRow = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                for (int r = 0, j = tempRow.FirstCellNum, len2 = tempRow.LastCellNum; j < len2; j++, r++)
                {

                    ICell cell = tempRow.GetCell(j);

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.String:
                                dataRow[r] = cell.StringCellValue;
                                break;
                            case CellType.Numeric:
                                dataRow[r] = cell.NumericCellValue;
                                break;
                            case CellType.Boolean:
                                dataRow[r] = cell.BooleanCellValue;
                                break;
                            default:
                                dataRow[r] = "ERROR";
                                break;
                        }
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

    }
}
